package j.framework.qmd.core;

import j.data.PagedData;
import j.framework.base.service.ApplicationService;
import j.framework.core.DictItem;
import j.framework.core.FApplication;
import j.framework.core.JUtils;
import j.framework.core.LoginUser;
import j.framework.qmd.jdbc.QueryMetadataService;
import j.framework.qmd.jdbc.QueryMetadataServiceImpl;
import j.util.*;
import j.util.jdbc.JdbcTemplate;
import j.util.jdbc.JdbcUtils;
import j.util.jdbc.QueryJdbcTemplate;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * User: jason
 * Date: 14-7-30 上午9:12
 */
public class QueryManager {
    private static QueryManager ourInstance = new QueryManager();

    private JdbcTemplate jdbcTemplate = SpringBeans.getBean(JdbcTemplate.class);

    private QueryMetadataService service = new QueryMetadataServiceImpl();

    public static QueryManager getInstance() {
        return ourInstance;
    }

    private Map<String,QueryMetadata> queryMetadataMap = New.map();   //以id做为key存储
    private Map<String,String> queryKeyIdMapper = New.map();

    private QueryManager() {
    }

    public static QueryMetadata getQueryMetadata(String key){
        String id = ourInstance.queryKeyIdMapper.get(key);
        if(StringUtils.isNullOrEmpty(id)){
            id = key;
        }
        QueryMetadata qmd = ourInstance.queryMetadataMap.get(id);
        if(qmd == null){
            synchronized (QueryManager.class){
                qmd = ourInstance.queryMetadataMap.get(id);
                if(qmd == null){
                    qmd = ourInstance.loadQueryMetadata(id);
                }
            }
        }
        return qmd;
    }

    private QueryMetadata loadQueryMetadata(String queryId){
        //从数据库中获取
        QueryMetadata qmd = ourInstance.service.getFullQueryMetadata(queryId);
        if(qmd != null){
           // queryMetadataMap.put(qmd.getQueryId(),qmd);
            if(StringUtils.isNotEmpty(qmd.getQueryKey())){
                //queryKeyIdMapper.put(qmd.getQueryKey(),qmd.getQueryId());
            }
        }
        return qmd;
    }

    public void refreshQueryMetadata(String queryId){
        queryMetadataMap.remove(queryId);
        loadQueryMetadata(queryId);
    }

    private static String buildQueryOrder(QueryMetadata qmd,String sort,String order){
        int j=0;
        StringBuilder sqlOrder = new StringBuilder();
        if(StringUtils.isNotEmpty(sort)){
            String[] orderArr = order.split(",");
            String[] sortColumns = sort.split(",");
            QueryFieldMetadata queryFieldMetadata;
            for(int i=0;i<sortColumns.length;i++){
                queryFieldMetadata = qmd.getFieldMetadata(sortColumns[i]);
                if(queryFieldMetadata!=null){
                    if(j++>0){
                        sqlOrder.append(",");
                    }
                    if(StringUtils.isNotEmpty(queryFieldMetadata.getTableAliasName())){
                        sqlOrder.append(queryFieldMetadata.getTableAliasName()).append(".");
                    }
                    sqlOrder.append(queryFieldMetadata.getFieldName());
                    order = null;
                    if(i<orderArr.length){
                        order = orderArr[i];
                    }
                    if(StringUtils.isNullOrEmpty(order)){
                        order = "asc";
                    }
                    sqlOrder.append(" ").append(order);
                }
            }
        }else if(qmd.getDefaultQuerySort()!=null && qmd.getDefaultQuerySort().size()>0){
            //根据默认排序列排序
            for(QuerySort qs : qmd.getDefaultQuerySort()){
                if(j++>0){
                    sqlOrder.append(",");
                }
                sqlOrder.append(qs.getColumnName()).append(" ").append(qs.getOrderBy());
            }
        }
        if(j>0){
            return " order by "+sqlOrder.toString();
        }
        return null;
    }


    public static QueryResult queryForList(LoginUser user,QueryMetadata qmd, Map<String, String> map)  {
        QueryResult queryResult = new QueryResult();
        QueryFieldMetadata queryFieldMetadata;
        QueryDataHandler handler = qmd.getQueryDataHandler(null);
        List<Map<String,Object>> listFooters = New.list();
        QueryJdbcTemplate qjt = new QueryJdbcTemplate(ourInstance.jdbcTemplate.getDataSource());
        try{
            boolean executeQuery = true;
            if(handler!=null){
                executeQuery = handler.beforeQuery(qjt,qmd,map,queryResult);
            }
            if(executeQuery){
                String _sql;
                List<Object> args;
                //构造排序
                String buildOrder = buildQueryOrder(qmd,map.get("sort"),map.get("order"));
                String where = null;
                if(qmd.isStoreProcedure()){
                    _sql = qmd.getQuerySql();
                    Map<String,Object> callParams = New.map();
                    if(handler!=null){
                        handler.prepareCall(user,map,callParams);
                    }else{
                        callParams.putAll(map);
                    }
                    //将查询条件加到参数中
                    callParams.put("orderBy",buildOrder);
                    TwoTuple<String,List<Object>> tuple = JdbcUtils.parseNamedSql(_sql, callParams);
                    _sql = tuple.first;
                    args = tuple.second;
                }else{
                    StringBuilder sql = new StringBuilder(qmd.getQuerySql());
                    args = New.list();
                    //构造where语句
                    if(handler!=null){
                        where = handler.buildWhereClause(user,map,args);
                        if(StringUtils.isNotEmpty(where)){
                            sql.append(" where ").append(where);
                        }
                    }else if(StringUtils.isNotEmpty(qmd.getDataHandlerClassName()) && StringUtils.isNotEmpty(qmd.getBuildWhereMethodName())){
                        try {
                            Object bean = qmd.getSpringBean();
                            Class<?> clazz = bean.getClass();
                            if(bean!=null){
                                Method m = clazz.getDeclaredMethod(qmd.getBuildWhereMethodName(),LoginUser.class,Map.class,List.class);
                                if(m!=null){
                                    Object result = m.invoke(bean,user,map,args);
                                    if(result!=null){
                                        where = result.toString();
                                        if(StringUtils.isNotEmpty(where)){
                                            sql.append(" where ").append(where);
                                        }
                                    }
                                }
                            }
                        }catch (Exception e){}
                    }
                    //构造排序语句
                    if(StringUtils.isNotEmpty(buildOrder)){
                        sql.append(buildOrder);
                    }
                    _sql = sql.toString();
                    if(qmd.isNamedSql()){
                        Map<String,Object> params = New.map();
                        params.putAll(map);
                        TwoTuple<String,List<Object>> tuple = JdbcUtils.parseNamedSql(sql.toString(), params);
                        _sql = tuple.first;
                        args = tuple.second;
                    }
                }
                //生成footer语句
                String footerSql = null;
                if(StringUtils.isNotEmpty(qmd.getFooterClause())){
                    footerSql = _sql;
                    footerSql = JdbcUtils.replaceSqlSelect(footerSql, qmd.getFooterClause());
                    /*if(StringUtils.isNotEmpty(where)){
                        //已经有where了
                        footerSql = footerSql + " where " + where;
                    }*/
                }
                if(qmd.isPagingSupported()){
                    int pageIndex = ConvertUtils.toInt(map.get("page"), 1);
                    int pageSize = ConvertUtils.toInt(map.get("rows"), 15);

                    PagedData<List<Map<String,Object>>> pd = qjt.queryForPagingListMapWithFullKey(_sql,footerSql,
                            qmd.getFieldMetadataList(),pageIndex,pageSize,listFooters,args.toArray());
                    queryResult.setList(pd.getDataItems());
                    queryResult.setTotalRecords(pd.getTotal());
                }else{
                    queryResult.setList(qjt.queryForListMapWithFullKey(_sql,footerSql,qmd.getFieldMetadataList(),listFooters,args.toArray()));
                    queryResult.setTotalRecords(queryResult.getList().size());
                }

            }
            //执行事后查询
            if(handler!=null){
                handler.doAfterQuery(qjt,qmd,map,queryResult);
            }
        }finally {
            qjt.close();
        }
        if(null == queryResult.getList() || queryResult.getList().size()==0){
            return queryResult;
        }
        //获取系统中所有的字典项、用户、部门，方便以后的格式化
        ApplicationService applicationService = SpringBeans.getBean(ApplicationService.class);
        Map<String,DictItem> allDictItems = applicationService.getAllDictItems();
        Map<String,DictItem> allUsers = applicationService.getAllUsers();
        Map<String,DictItem> allDepts = applicationService.getAllDepts();
        String firstTotalFooterKey = null;
        //处理map中的key
        List<Map<String,Object>> newList = New.list();
        Map<String,Object> newData;
        Object v;
        String key;
        DictItem dictItem;
        Map<String,Object> rowData = New.map();
        StringBuilder opt;
        String formatWhenTrue = FApplication.getSetting("format_when_true");
        String formatWhenFalse = FApplication.getSetting("format_when_false");
        boolean canFormat;
        boolean booleanValue;
        for(Map<String,Object> item : queryResult.getList()){
            newData = New.map(item.size());
            //加密主键
            if(null!=qmd.getPrimaryKeys() && qmd.getPrimaryKeys().size()>0){
                if(qmd.getPrimaryKeys().size()==1){
                    item.put("desId", DES.encrypt(String.valueOf(item.get(qmd.getPrimaryKeys().get(0).getColumnName()))));
                }
            }
            if(qmd.isOperatorSupported() && null!=handler){
                newData.put("opt",handler.getOperatorHtml(user,item));
            }else if(qmd.isOperatorSupported()){
                //显示操作，但没有定义handler，则检查是否通过配置增加了操作
                if(qmd.getOperations()!=null && qmd.getOperations().size()>0){
                    opt = new StringBuilder();
                    for(QueryOperation qo : qmd.getOperations()){
                        rowData.clear();
                        rowData.putAll(item);
                        if (qo.getCustomParametersMap() != null && qo.getCustomParametersMap().size() > 0) {
                            rowData.putAll(qo.getCustomParametersMap());
                        }
                        if(StringUtils.isNotEmpty(qo.getGenerateMethodName())){
                            //调用此方法来生成操作
                            Object bean = qmd.getSpringBean();
                            if(bean!=null){
                                Class<?> clazz = bean.getClass();
                                QueryActionContext qac = new QueryActionContext(qo.getActionId(),qo.getActionName(),rowData);
                                try {
                                    Method m = clazz.getDeclaredMethod(qo.getGenerateMethodName(), LoginUser.class,QueryActionContext.class);
                                    Object result = m.invoke(bean, user, qac);
                                    if (result != null) {
                                        opt.append(result.toString());
                                    }
                                }catch (NoSuchMethodException e){
                                    throw new RuntimeException(String.format("SpringBean[%s]没有定义[%s]方法，请确认！",
                                            qmd.getDataHandlerClassName(),qo.getGenerateMethodName()));
                                } catch (InvocationTargetException e) {
                                    throw new RuntimeException(e.getMessage());
                                } catch (IllegalAccessException ignored) {}
                            }else{
                                throw new RuntimeException(String.format("查询[%s]没有定义一个SpringBean有[%s]的方法，请确认!",
                                        qmd.getName(),qo.getGenerateMethodName()));
                            }
                        }else {
                            opt.append("<a href=\"javascript:void(0);\" onclick=\"");
                            opt.append(StringUtils.format(qo.getOnClick(), rowData));
                            opt.append("\">").append(qo.getActionName()).append("</a>");
                        }
                        opt.append(" ");
                    }
                    opt.deleteCharAt(opt.length()-1);
                    newData.put("opt",opt.toString());
                }
            }
            for(Map.Entry<String,Object> entry : item.entrySet()){
                queryFieldMetadata = qmd.getFieldMetadataByFieldName(entry.getKey());
                if(null == queryFieldMetadata){
                    if("desId".equals(entry.getKey())){
                        newData.put("desId",entry.getValue());
                    }
                    continue;
                }
                canFormat = !queryFieldMetadata.getFormatType().equals(FormatType.NONE);
                if(queryFieldMetadata!=null){
                    key = queryFieldMetadata.getFieldId();
                    if(queryFieldMetadata.isUsedFieldNameAsKey()){
                        key = queryFieldMetadata.getFieldName();
                    }
                    if(!qmd.isPagingSupported() && StringUtils.isNullOrEmpty(firstTotalFooterKey) && queryFieldMetadata.getWidth()>=100){
                        firstTotalFooterKey = queryFieldMetadata.getFieldName();
                    }
                    if(queryFieldMetadata.isPrimaryKey()){
                        newData.put(key,entry.getValue());
                        continue;
                    }
                    if(handler!=null && !canFormat){
                        v = handler.getColumnValue(queryFieldMetadata,entry.getValue(),item);
                    }else{
                        v = entry.getValue();
                    }
                    if(qmd.isShowByTree() && queryFieldMetadata.isAsTreeParentField()){
                        key = "_parentId";
                    }
                    if(v==null){
                        v = "";
                    }else if(canFormat){
                        if(queryFieldMetadata.getFormatType().equals(FormatType.BOOLEAN)){
                            //v = JUtils.formatBoolean(String.valueOf(v));
                            booleanValue = ConvertUtils.toBoolean(String.valueOf(v));
                            if(booleanValue){
                                v = formatWhenTrue;
                            }else{
                                v = formatWhenFalse;
                            }
                        }else if(queryFieldMetadata.getFormatType().equals(FormatType.DICTIONARY)) {
                            //格式化字典表
                            dictItem = allDictItems.get(queryFieldMetadata.getDictionaryId()+"_"+String.valueOf(v));

                            if(dictItem!=null){
                                v = dictItem.getName();
                            }
                        }else if(queryFieldMetadata.getFormatType().equals(FormatType.USER)){
                            //格式化用户
                            dictItem = allUsers.get(String.valueOf(v));
                            if(dictItem!=null){
                                v = dictItem.getName();
                            }
                        } else if(queryFieldMetadata.getFormatType().equals(FormatType.DEPT)){
                            //格式化部门
                            dictItem = allDepts.get(String.valueOf(v));
                            if(dictItem!=null){
                                v = dictItem.getName();
                            }
                        } else if(queryFieldMetadata.getFormatType().equals(FormatType.DATE)){
                            //格式化时间
                            if(StringUtils.isNotEmpty(queryFieldMetadata.getFormatOptions())){
                                v = DateUtils.format((Date) v, queryFieldMetadata.getFormatOptions());
                            }
                        } else if(queryFieldMetadata.getFormatType().equals(FormatType.DIGITAL)){
                            //格式化数字
                            if(StringUtils.isNotEmpty(queryFieldMetadata.getFormatOptions())){
                                NumberFormat numberFormat = new DecimalFormat(queryFieldMetadata.getFormatOptions());
                                v = numberFormat.format(v);
                            }
                        } else if(queryFieldMetadata.getFormatType().equals(FormatType.CURRENCY)){
                            //格式化货币，仅显示千分位
                            DecimalFormat d1 =new DecimalFormat("#,##0.##;(#)");
                            v = d1.format(v);
                        }
                    }
                    newData.put(key,v);
                }
            }
            newList.add(newData);
        }
        queryResult.getList().clear();
        queryResult.setList(newList);
        boolean needAddTotalFooter = false;
        if(listFooters.size()==0){
            //没有配置footer
            if(queryResult.getFooter()==null || queryResult.getFooter().size()==0) {
                //没有定义footer，则为不分页的grid增加footer合计行，前提必须得显示footer
                if (qmd.isFooterSupported() && !qmd.isPagingSupported()) {
                    needAddTotalFooter = true;
                }
            }
        }
        if(needAddTotalFooter){
            List<Map<String, Object>> listFooter = New.list();
            Map<String,Object> footer = New.map();
            if(qmd.isOperatorSupported()){
                //在合计行上增加计数footer
                footer.put("opt","合计："+queryResult.getTotalRecords());
            }else if(StringUtils.isNotEmpty(firstTotalFooterKey)){
                //在从左到右的第一个宽度大于100的列上增加计数footer
                footer.put(firstTotalFooterKey,"合计："+queryResult.getTotalRecords());
            }
            listFooter.add(footer);
            queryResult.setFooter(listFooter);
        }
        //处理结果footer中的key
        if(queryResult.getFooter()!=null && queryResult.getFooter().size()>0){
            //如果开发人员在实现类中实现了footer，则不再使用系统定义的footer
            listFooters = queryResult.getFooter();
        }else if(listFooters.size()>0){
            //系统实现的footer
            Map<String,Object> footer = listFooters.get(0);
            Map<String,Object> tmpFooter;
            listFooters.clear();
            Map<Integer,Map<String,Object>> mapFooters = new TreeMap<Integer,Map<String,Object>>();
            QueryFieldMetadata qfmd;
            for(QueryFieldFooter f : qmd.getFooters()){
                qfmd = qmd.getFieldMetadata(f.getFieldId());
                if(qfmd == null){
                    continue;
                }
                key=qfmd.getFieldName();
                v = footer.get(key);
                if(v!=null){
                    tmpFooter = mapFooters.get(f.getRowNumber());
                    if(tmpFooter == null){
                        tmpFooter = New.map();
                        mapFooters.put(f.getRowNumber(),tmpFooter);
                    }
                    tmpFooter.put(key,v);
                }
            }
            //将mapFooters整理到listFooters中
            listFooters.addAll(mapFooters.values());
        }
        if(listFooters.size()>0){
            newList = New.list();
            //若是treegrid，在处理footer时，需要判断在树字段列上是否定义了footer，若没是定义，需要将其值设为空，否则前台页面上会显示"undefined"
            DataColumn treeField=null;
            if(qmd.isShowByTree()){
                treeField = qmd.getTreeField();
            }
            for(Map<String,Object> item : listFooters){
                newData = New.map(item.size());
                for(Map.Entry<String,Object> entry : item.entrySet()){
                    if(entry.getKey().equals("opt")){
                        newData.put(entry.getKey(),entry.getValue());
                        continue;
                    }
                    queryFieldMetadata = qmd.getFieldMetadataByFieldName(entry.getKey());
                    if(queryFieldMetadata!=null){
                        newData.put(queryFieldMetadata.getFieldId(),entry.getValue());
                    }
                }
                if(treeField!=null){
                    if(!newData.containsKey(treeField.getDataColumnId())){
                        //系统没有在此treegrid这行的footer上定义内容，则将此内容设为空，并加到footer上
                        newData.put(treeField.getFieldKey(),"");
                    }
                }
                newList.add(newData);
            }

            queryResult.setFooter(newList);
        }
        return queryResult;
    }
}
